﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using HHMMS.FileServer;

namespace HHMMS.FileServer
{
	/// <summary>
	///封装了对表AttachmentInfo的数据库操作的方法。
	/// </summary>
    public class AttachmentInfoProvider
    {
		#region 字段属性
		//默认数据库连接字符串名称
		private readonly string connectionName = ConfigurationManager.AppSettings["DefaultConnectionName"];
		
		private static AttachmentInfoProvider instance = null;
		/// <summary>
		/// 获取AttachmentInfoProvider类的唯一实例。
		/// </summary>
        public static AttachmentInfoProvider Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (typeof(AttachmentInfoProvider))
                    {
                        instance = new AttachmentInfoProvider();
                    }
                }
                return instance;
            }
        }
		
		/// <summary>
		/// 私有构造函数。
		/// </summary>
		private AttachmentInfoProvider()
		{}
		#endregion
		
		#region 基本操作
		///<summary>
		///删除表AttachmentInfo中的指定记录并返回状态。
		///</summary>
		///<param name="iD">附件ID</param>
		///<returns>删除成功则返回true，否则返回false</returns>
		public bool Delete(string iD)
		{
			string sql = "DELETE [AttachmentInfo] WHERE ID = @ID";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, iD);
			return db.ExecuteNonQuery(cmd) > 0;
		}
		
		///<summary>
		///向表AttachmentInfo中插入一条记录并返回状态。
		///</summary>
		///<param name="attachmentInfo">要插入记录的AttachmentInfo实例</param>
		///<returns>返回true或false</returns>
		public bool Insert(AttachmentInfo attachmentInfo)
		{
			string sql = "INSERT INTO [AttachmentInfo] (ID, TypeID, FileName, FileUrl, FileSize, FilePath, CreatorID, CreatorName, CreateDate, Mark1, Mark2, Mark3, Mark4) VALUES (@ID, @TypeID, @FileName, @FileUrl, @FileSize, @FilePath, @CreatorID, @CreatorName, @CreateDate, @Mark1, @Mark2, @Mark3, @Mark4)";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, attachmentInfo.ID);
			db.AddInParameter(cmd, "@TypeID", DbType.String, attachmentInfo.TypeID);
			db.AddInParameter(cmd, "@FileName", DbType.String, attachmentInfo.FileName);
			db.AddInParameter(cmd, "@FileUrl", DbType.AnsiString, attachmentInfo.FileUrl);
			db.AddInParameter(cmd, "@FileSize", DbType.Decimal, attachmentInfo.FileSize);
			db.AddInParameter(cmd, "@FilePath", DbType.String, attachmentInfo.FilePath);
			db.AddInParameter(cmd, "@CreatorID", DbType.AnsiStringFixedLength, attachmentInfo.CreatorID);
			db.AddInParameter(cmd, "@CreatorName", DbType.String, attachmentInfo.CreatorName);
			db.AddInParameter(cmd, "@CreateDate", DbType.DateTime, attachmentInfo.CreateDate);
			db.AddInParameter(cmd, "@Mark1", DbType.String, attachmentInfo.Mark1);
			db.AddInParameter(cmd, "@Mark2", DbType.String, attachmentInfo.Mark2);
			db.AddInParameter(cmd, "@Mark3", DbType.String, attachmentInfo.Mark3);
			db.AddInParameter(cmd, "@Mark4", DbType.String, attachmentInfo.Mark4);
			return db.ExecuteNonQuery(cmd) > 0;
		}
		
		///<summary>
		///更新表AttachmentInfo中指定主码的某条记录。
		///</summary>
		///<param name="attachmentInfo">要更新记录的AttachmentInfo实例</param>
		///<returns>更新成功则返回true，否则返回false。</returns>
		public bool Update(AttachmentInfo attachmentInfo)
		{
			string sql = "UPDATE [AttachmentInfo] SET  TypeID = @TypeID,  FileName = @FileName,  FileUrl = @FileUrl,  FileSize = @FileSize,  FilePath = @FilePath,  CreatorID = @CreatorID,  CreatorName = @CreatorName,  CreateDate = @CreateDate,  Mark1 = @Mark1,  Mark2 = @Mark2,  Mark3 = @Mark3,  Mark4 = @Mark4 WHERE  ID = @ID";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, attachmentInfo.ID);
			db.AddInParameter(cmd, "@TypeID", DbType.String, attachmentInfo.TypeID);
			db.AddInParameter(cmd, "@FileName", DbType.String, attachmentInfo.FileName);
			db.AddInParameter(cmd, "@FileUrl", DbType.AnsiString, attachmentInfo.FileUrl);
			db.AddInParameter(cmd, "@FileSize", DbType.Decimal, attachmentInfo.FileSize);
			db.AddInParameter(cmd, "@FilePath", DbType.String, attachmentInfo.FilePath);
			db.AddInParameter(cmd, "@CreatorID", DbType.AnsiStringFixedLength, attachmentInfo.CreatorID);
			db.AddInParameter(cmd, "@CreatorName", DbType.String, attachmentInfo.CreatorName);
			db.AddInParameter(cmd, "@CreateDate", DbType.DateTime, attachmentInfo.CreateDate);
			db.AddInParameter(cmd, "@Mark1", DbType.String, attachmentInfo.Mark1);
			db.AddInParameter(cmd, "@Mark2", DbType.String, attachmentInfo.Mark2);
			db.AddInParameter(cmd, "@Mark3", DbType.String, attachmentInfo.Mark3);
			db.AddInParameter(cmd, "@Mark4", DbType.String, attachmentInfo.Mark4);
			return db.ExecuteNonQuery(cmd) > 0;
		}
		#endregion
		
		#region 获取对象
		///<summary>
		///获取表AttachmentInfo中指定主码的某条记录的实例。
		///</summary>
		///<param name="iD">附件ID</param>
		///<returns>返回记录的实例AttachmentInfo</returns>
		public AttachmentInfo SelectSingle(string iD)
		{
			string sql = "SELECT * FROM [AttachmentInfo] WHERE ID = @ID";
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
			db.AddInParameter(cmd, "@ID", DbType.AnsiStringFixedLength, iD);
			using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr != null && dr.Read())
                {
                    return this.BuildEntity(dr);
                }
            }
			return null;
		}
		
		///<summary>
		///根据SQL查询条件返回行实例的集合。
		///</summary>
		///<param name="where">带"WHERE"的SQL查询条件</param>
		///<param name="whereParameters">条件中的参数信息数组</param>
		///<returns>返回的实例AttachmentInfoList对象。</returns>
		public List<AttachmentInfo> SelectMany(string where, params DbParameter[] dbParameters)
		{
			List<AttachmentInfo> attachmentInfoList = new List<AttachmentInfo>();
			string sql = string.Format("SELECT * FROM [AttachmentInfo] {0}", where);
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            if(dbParameters != null && dbParameters.Length > 0)
			{
            	cmd.Parameters.AddRange(dbParameters);
			}
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr != null)
                {
                	while(dr.Read())
					{
						attachmentInfoList.Add(this.BuildEntity(dr));
					}
                }
            }
			return attachmentInfoList;
		}
		
		///<summary>
		///返回表AttachmentInfo所有行实例的集合。
		///</summary>
		///<returns>返回所有行实例AttachmentInfoList对象。</returns>
		public List<AttachmentInfo> SelectAll()
		{
			return this.SelectMany(string.Empty);
		}
		
		///<summary>
		/// 根据条件和排序按页返回表AttachmentInfo的行实例的集合。
		///</summary>
		///<param name="pageSize">每页显示的条数</param>
		///<param name="pageIndex">页索引(1开始)</param>
		///<param name="where">带"WHERE"的SQL查询条件</param>
		///<param name="order">带"ORDER"的SQL排序视图达式</param>
		///<param name="whereParameters">WHERE中的参数数组</param>
		///<param name="recordCount">返回记录总数</param>
		///<returns>返回某页的所有行实例AttachmentInfo的集合</returns>
		public List<AttachmentInfo> SelectPagedResult(int pageSize, int pageIndex, string where, string order, 
			params DbParameter[] whereParameters)
		{
			if(string.IsNullOrEmpty(order))
				order = "ORDER BY ID DESC";
			
			string sql = @"WITH tmp AS
				(
					SELECT *,ROW_NUMBER() OVER({0}) AS  'RowNumber' FROM [AttachmentInfo] {1}
				)
				SELECT * FROM tmp WHERE RowNumber BETWEEN {2} AND {3}";
			
			sql = string.Format(sql, order, where, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
			
			List<AttachmentInfo> attachmentInfoList = new List<AttachmentInfo>();
			
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            if(whereParameters != null && whereParameters.Length > 0)
			{
            	cmd.Parameters.AddRange(whereParameters);
			}
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr != null)
                {
                	while(dr.Read())
					{
						attachmentInfoList.Add(this.BuildEntity(dr));
					}
                }
            }
			return attachmentInfoList;
		}
		
		///<summary>
		/// 根据条件和排序按页返回表AttachmentInfo的行数。
		///</summary>
		///<param name="where">带where的查询条件</param>
		///<returns>返回行数</returns>
		public int GetRecordCount(string where, params DbParameter[] whereParameters)
		{
			int recordCount = 0;
			Database db = DatabaseFactory.CreateDatabase(connectionName);
            DbCommand cmd = db.GetSqlStringCommand("SELECT COUNT(1) FROM dbo.AttachmentInfo "+ where);
            if(whereParameters != null && whereParameters.Length > 0)
			{
            	cmd.Parameters.AddRange(whereParameters);
			}
			using (IDataReader dr = db.ExecuteReader(cmd))
            {
				if(dr != null)
				{
					if (dr.Read()) 
					{
						if(!dr.IsDBNull(0)) recordCount = dr.GetInt32(0);
					}
				}
			}
			return recordCount;
		}
		
		///<summary>
		/// 将IDataReader转换为实体。
		///</summary>
		///<param name="dr">IDataReader当前待读取的行</param>
		///<returns>返回AttachmentInfo实例</returns>
		private AttachmentInfo BuildEntity(IDataReader dr)
		{
			AttachmentInfo attachmentInfo = new AttachmentInfo();
			if(dr["ID"] != DBNull.Value) attachmentInfo.ID = dr.GetString(dr.GetOrdinal("ID"));
			if(dr["TypeID"] != DBNull.Value) attachmentInfo.TypeID = dr.GetString(dr.GetOrdinal("TypeID"));
			if(dr["FileName"] != DBNull.Value) attachmentInfo.FileName = dr.GetString(dr.GetOrdinal("FileName"));
			if(dr["FileUrl"] != DBNull.Value) attachmentInfo.FileUrl = dr.GetString(dr.GetOrdinal("FileUrl"));
			if(dr["FileSize"] != DBNull.Value) attachmentInfo.FileSize = dr.GetDecimal(dr.GetOrdinal("FileSize"));
			if(dr["FilePath"] != DBNull.Value) attachmentInfo.FilePath = dr.GetString(dr.GetOrdinal("FilePath"));
			if(dr["CreatorID"] != DBNull.Value) attachmentInfo.CreatorID = dr.GetString(dr.GetOrdinal("CreatorID"));
			if(dr["CreatorName"] != DBNull.Value) attachmentInfo.CreatorName = dr.GetString(dr.GetOrdinal("CreatorName"));
			if(dr["CreateDate"] != DBNull.Value) attachmentInfo.CreateDate = dr.GetDateTime(dr.GetOrdinal("CreateDate"));
			if(dr["Mark1"] != DBNull.Value) attachmentInfo.Mark1 = dr.GetString(dr.GetOrdinal("Mark1"));
			if(dr["Mark2"] != DBNull.Value) attachmentInfo.Mark2 = dr.GetString(dr.GetOrdinal("Mark2"));
			if(dr["Mark3"] != DBNull.Value) attachmentInfo.Mark3 = dr.GetString(dr.GetOrdinal("Mark3"));
			if(dr["Mark4"] != DBNull.Value) attachmentInfo.Mark4 = dr.GetString(dr.GetOrdinal("Mark4"));
			return attachmentInfo;
		}
		#endregion
		
		#region 扩展方法
        /// <summary>
        /// 通过扩展标识获取附件信息。
        /// </summary>
        /// <param name="marks"></param>
        /// <returns></returns>
        public List<AttachmentInfo> Select(string typeID, params string[] marks)
        {
            string where = "WHERE 1=1";
            IList<DbParameter> param = new List<DbParameter>();
            
            if (!string.IsNullOrEmpty(typeID))
            {
                where += " AND TypeID = @TypeID";
                param.Add(new SqlParameter("@TypeID", typeID));
            }

            if (marks != null && marks.Length > 0)
            {
                for (int i = 0; i < marks.Length; i++)
                {
                    where += string.Format(" AND Mark{0} = @Mark{0}", i + 1);
                    param.Add(new SqlParameter("@Mark" + (i + 1), marks[i]));
                }
            }
            return this.SelectMany(where, param.ToArray());
        }
		#endregion
    }
}